﻿using DTO;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAO
{
    public class ThietBiDienDAO
    {
/*-------------------------LOAD----------------------------------------------------
LOADTBDIEN
LOADTBDIEN_STT @STT int
LOADTBDIEN_LOAITB @LoaiTB int
LOADTBDIEN_MATHONGSO @MaThongSo int*/
//LOADBOKICHDIEN

        public DataTable LOADLOAITHIETBIDIEN()
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlDataAdapter _da = new SqlDataAdapter("LOADLOAITHIETBIDIEN", _cn);
            _da.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;

            DataTable _dt = new DataTable();
            _da.Fill(_dt);
            return _dt;
        }

        public DataTable LOADTHIETBIDIEN()
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlDataAdapter _da = new SqlDataAdapter("LOADTHIETBIDIEN", _cn);
            _da.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;

            DataTable _dt = new DataTable();
            _da.Fill(_dt);
            return _dt;
        }

        public void THEMTHIETBIDIEN(ThietBiDienDTO tbdDTO)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("THEMTHIETBIDIEN", _cn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@NgayNhap", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@MaTB", SqlDbType.Char, 20));
            cmd.Parameters.Add(new SqlParameter("@TenTB", SqlDbType.NVarChar, 50));
            //cmd.Parameters.Add(new SqlParameter("@Vol", SqlDbType.Float));
            //cmd.Parameters.Add(new SqlParameter("@Amp", SqlDbType.Decimal));
            cmd.Parameters.Add(new SqlParameter("@GhiChu", SqlDbType.NVarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@CSKhaDung", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@CSHaoHut", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@HSDeBa", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@DonGia", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@MaLoai", SqlDbType.Int));

            cmd.Parameters["@NgayNhap"].Value = tbdDTO.NGAYNHAP;
            cmd.Parameters["@MaTB"].Value = tbdDTO.MATB;
            cmd.Parameters["@TenTB"].Value = tbdDTO.TENTB;
            //cmd.Parameters["@Vol"].Value = tbdDTO.VOL;
            //cmd.Parameters["@Amp"].Value = tbdDTO.AMP;
            cmd.Parameters["@GhiChu"].Value = tbdDTO.GHICHU;
            cmd.Parameters["@CSKhaDung"].Value = tbdDTO.CSKHADUNG;
            cmd.Parameters["@CSHaoHut"].Value = tbdDTO.CSHAOHUT;
            cmd.Parameters["@HSDeBa"].Value = tbdDTO.HSDEBA;
            cmd.Parameters["@DonGia"].Value = tbdDTO.DONGIA;
            cmd.Parameters["@MaLoai"].Value = tbdDTO.MALOAI;
            //int _kq = cmd.ExecuteNonQuery();
            cmd.ExecuteNonQuery();
            _cn.Close();
            // return _kq;
        }

        public int SUATHIETBIDIEN(ThietBiDienDTO tbdDTO)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("SUATHIETBIDIEN", _cn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@STT", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@NgayNhap", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@MaTB", SqlDbType.Char, 20));
            cmd.Parameters.Add(new SqlParameter("@TenTB", SqlDbType.NVarChar, 50));
            //cmd.Parameters.Add(new SqlParameter("@Vol", SqlDbType.Float));
            //cmd.Parameters.Add(new SqlParameter("@Amp", SqlDbType.Decimal));
            //cmd.Parameters.Add(new SqlParameter("@GhiChu", SqlDbType.NVarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@CSKhaDung", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@CSHaoHut", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@HSDeBa", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@DonGia", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@MaLoai", SqlDbType.Int));

            cmd.Parameters["@STT"].Value = tbdDTO.STT;
            cmd.Parameters["@NgayNhap"].Value = tbdDTO.NGAYNHAP;
            cmd.Parameters["@MaTB"].Value = tbdDTO.MATB;
            cmd.Parameters["@TenTB"].Value = tbdDTO.TENTB;
            //cmd.Parameters["@Vol"].Value = tbdDTO.VOL;
            //cmd.Parameters["@Amp"].Value = tbdDTO.AMP;
            //cmd.Parameters["@GhiChu"].Value = tbdDTO.GHICHU;
            cmd.Parameters["@CSKhaDung"].Value = tbdDTO.CSKHADUNG;
            cmd.Parameters["@CSHaoHut"].Value = tbdDTO.CSHAOHUT;
            cmd.Parameters["@HSDeBa"].Value = tbdDTO.HSDEBA;
            cmd.Parameters["@DonGia"].Value = tbdDTO.DONGIA;
            cmd.Parameters["@MaLoai"].Value = tbdDTO.MALOAI;

            int _kq = cmd.ExecuteNonQuery();
            //cmd.ExecuteNonQuery();
            _cn.Close();
            return _kq;
        }

        public int XOATHIETBIDIEN(int STT)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("XOATHIETBIDIEN", _cn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@STT", SqlDbType.Int));
            cmd.Parameters["@STT"].Value = STT;
            int _kq = cmd.ExecuteNonQuery();
            _cn.Close();
            return _kq;
        }

        //************************************************************/

        public DataTable LoadBoKichDien()
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlDataAdapter _da = new SqlDataAdapter("LOADBOKICHDIEN", _cn);
            _da.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;

            DataTable _dt = new DataTable();
            _da.Fill(_dt);
            return _dt;
        }

        public DataTable LoadMachDieuKhien()
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlDataAdapter _da = new SqlDataAdapter("LOAD_MACHDIEUKHIEN", _cn);
            _da.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataTable _dt = new DataTable();
            _da.Fill(_dt);
            return _dt;
        }

        public DataTable LoadTBDien()
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlDataAdapter _da = new SqlDataAdapter("LOADTBDIEN", _cn);
            _da.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataTable _dt = new DataTable();
            _da.Fill(_dt);
            return _dt;
        }
        public DataTable LoadTBDien_STT(int STT)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlDataAdapter _da = new SqlDataAdapter("LOADTBDIEN_STT", _cn);
            _da.SelectCommand.CommandType = CommandType.StoredProcedure;
            _da.SelectCommand.Parameters.Add("@STT", SqlDbType.Int).Value = STT;

            DataTable _dt = new DataTable();
            _da.Fill(_dt);
            return _dt;
        }
        public DataTable LoadTBDien_LoaiTB(int LoaiTB)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlDataAdapter _da = new SqlDataAdapter("LOADTBDIEN_LOAITB", _cn);
            _da.SelectCommand.CommandType = CommandType.StoredProcedure;
            _da.SelectCommand.Parameters.Add("@LoaiTB", SqlDbType.Int).Value = LoaiTB;

            DataTable _dt = new DataTable();
            _da.Fill(_dt);
            return _dt;
        }
        public DataTable LoadTBDien(int MaThongSo)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlDataAdapter _da = new SqlDataAdapter("LOADTBDIEN_MATHONGSO", _cn);
            _da.SelectCommand.CommandType = CommandType.StoredProcedure;
            _da.SelectCommand.Parameters.Add("@MaThongSo", SqlDbType.Int).Value = MaThongSo;

            DataTable _dt = new DataTable();
            _da.Fill(_dt);
            return _dt;
        }

/*-------------------------INSERT----------------------------------------------------
THEMTBDIEN
	@Vol decimal,
	@Amp decimal,
	@CSHaoHut int,
	@HSDeBa int,
	@GhiChu nvarchar(50),
	@MaThongSo int,
	@LoaiTB int
 */
//THEMBOKICHDIEN

        public void ThemThietBiDien(ThietBiDienDTO tbd)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("THEMTBDIEN", _cn);
            cmd.CommandType = CommandType.StoredProcedure;

           // ThongSoChungDAO _tscDAO = new ThongSoChungDAO();
           // int maTS = _tscDAO.ThemThongSo(ts);

            cmd.Parameters.Add(new SqlParameter("@Vol", SqlDbType.Float));
            cmd.Parameters.Add(new SqlParameter("@Amp", SqlDbType.Decimal));
            cmd.Parameters.Add(new SqlParameter("@CSHaoHut", SqlDbType.Decimal));
            cmd.Parameters.Add(new SqlParameter("@HSDeBa", SqlDbType.Decimal));
            cmd.Parameters.Add(new SqlParameter("@GhiChu", SqlDbType.NVarChar,50));
            cmd.Parameters.Add(new SqlParameter("@MaThongSo", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@LoaiTB", SqlDbType.Int));
            cmd.Parameters["@Vol"].Value = tbd.VOL;
            cmd.Parameters["@Amp"].Value = tbd.AMP;
            cmd.Parameters["@CSHaoHut"].Value = tbd.CSHAOHUT;
            cmd.Parameters["@HSDeBa"].Value = tbd.HSDEBA;
            cmd.Parameters["@GhiChu"].Value = tbd.GHICHU;
            cmd.Parameters["@MaThongSo"].Value = tbd.MATHONGSO;
            cmd.Parameters["@LoaiTB"].Value = tbd.LOAITB;
            //int _kq = cmd.ExecuteNonQuery();
            cmd.ExecuteNonQuery();
            _cn.Close();
           // return _kq;
        }



        public void THEMBOKICHDIEN(ThietBiDienDTO tbd)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("THEMBOKICHDIEN", _cn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@NgayNhap", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@MaTB", SqlDbType.Char, 20));
            cmd.Parameters.Add(new SqlParameter("@TenTB", SqlDbType.NVarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@Vol", SqlDbType.Float));
            cmd.Parameters.Add(new SqlParameter("@GhiChu", SqlDbType.NVarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@CSKhaDung", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@DonGia", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@MaLoai", SqlDbType.Int));


            cmd.Parameters["@NgayNhap"].Value = tbd.NGAYNHAP;
            cmd.Parameters["@MaTB"].Value = tbd.MATB;
            cmd.Parameters["@TenTB"].Value = tbd.TENTB;
            cmd.Parameters["@Vol"].Value = tbd.VOL;
            cmd.Parameters["@GhiChu"].Value = tbd.GHICHU;
            cmd.Parameters["@CSKhaDung"].Value = tbd.CSKHADUNG;
            cmd.Parameters["@DonGia"].Value = tbd.DONGIA;
            cmd.Parameters["@MaLoai"].Value = tbd.MALOAI;
            //int _kq = cmd.ExecuteNonQuery();
            cmd.ExecuteNonQuery();
            _cn.Close();
            // return _kq;
        }



        public void THEMMACHSAC(ThietBiDienDTO tbd)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("THEMMACHSAC", _cn);
            cmd.CommandType = CommandType.StoredProcedure;

            // ThongSoChungDAO _tscDAO = new ThongSoChungDAO();
            // int maTS = _tscDAO.ThemThongSo(ts);

            cmd.Parameters.Add(new SqlParameter("@Vol", SqlDbType.Float));
            cmd.Parameters.Add(new SqlParameter("@Amp", SqlDbType.Decimal));
            cmd.Parameters.Add(new SqlParameter("@CSHaoHut", SqlDbType.Decimal));
            cmd.Parameters.Add(new SqlParameter("@HSDeBa", SqlDbType.Decimal));
            cmd.Parameters.Add(new SqlParameter("@GhiChu", SqlDbType.NVarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@MaThongSo", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@LoaiTB", SqlDbType.Int));
            cmd.Parameters["@Vol"].Value = tbd.VOL;
            cmd.Parameters["@Amp"].Value = tbd.AMP;
            cmd.Parameters["@CSHaoHut"].Value = tbd.CSHAOHUT;
            cmd.Parameters["@HSDeBa"].Value = tbd.HSDEBA;
            cmd.Parameters["@GhiChu"].Value = tbd.GHICHU;
            cmd.Parameters["@MaThongSo"].Value = tbd.MATHONGSO;
            cmd.Parameters["@LoaiTB"].Value = tbd.LOAITB;
            //int _kq = cmd.ExecuteNonQuery();
            cmd.ExecuteNonQuery();
            _cn.Close();
            // return _kq;
        }
/*-------------------------UPDATE----------------------------------------------------
SUATBDIEN @STT int,
	@Vol decimal,
	@Amp decimal,
	@CSHaoHut int,
	@HSDeBa int,
	@GhiChu nvarchar(50),
	@LoaiTB int
 */
        //Chỉnh sửa thông số của thiết bị điện
        public int SuaThietBiDien(ThietBiDienDTO tbd)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("SUATBDIEN", _cn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@STT", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@Vol", SqlDbType.Float));
            cmd.Parameters.Add(new SqlParameter("@Amp", SqlDbType.Decimal));
            cmd.Parameters.Add(new SqlParameter("@CSHaoHut", SqlDbType.Decimal));
            cmd.Parameters.Add(new SqlParameter("@HSDeBa", SqlDbType.Decimal));
            cmd.Parameters.Add(new SqlParameter("@GhiChu", SqlDbType.NVarChar,50));
            cmd.Parameters.Add(new SqlParameter("@LoaiTB", SqlDbType.Int));
     
            cmd.Parameters["@STT"].Value = tbd.STT;
            cmd.Parameters["@Vol"].Value = tbd.VOL;
            cmd.Parameters["@Amp"].Value = tbd.AMP;
            cmd.Parameters["@CSHaoHut"].Value = tbd.CSHAOHUT;
            cmd.Parameters["@HSDeBa"].Value = tbd.HSDEBA;
            cmd.Parameters["@GhiChu"].Value = tbd.GHICHU;
            cmd.Parameters["@LoaiTB"].Value = tbd.LOAITB;
 
            int _kq = cmd.ExecuteNonQuery();
            _cn.Close();
            return _kq;
        }
/*-------------------------DELETE----------------------------------------------------
XOATBDIEN @STT int         
*/
        public int XoaThietBiDien(int STT)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("XOATBDIEN", _cn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@STT", SqlDbType.Int));
            cmd.Parameters["@STT"].Value = STT;
            int _kq = cmd.ExecuteNonQuery();
            _cn.Close();
            return _kq;
        }

    }
}
